﻿using sMES_Base;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using sMES_Base.service;
using unload_acc_from_machine_process.Models;

namespace unload_acc_from_machine_process
{
    /// <summary>
	/// 元件[unload_acc_from_machine_process]
    /// <auth>JingHuang</auth><time>2018/12/27 上午 10:32:01</time>
    /// <description></description>
    /// <list>
    ///     <modify>
    ///         <auth></auth><mantis>0053949</mantis><time></time>
    ///         <description></description>
    ///     </modify>
    ///     <modify>
    ///         <auth>Rex</auth><mantis>0055887</mantis><time></time>
    ///         <description>治具上下模時對tblWIPCont_Accessory的處理</description>
    ///     </modify>
    /// </list>
    /// </summary>
    public class unload_acc_from_machine_process_utility : BaseUtility
    {
        public unload_acc_from_machine_process_utility(ref BaseService CurrentService) : base(ref CurrentService)
        {
        }

        public RES_unload_acc_from_machine_process unload_acc_from_machine_process(REQ_unload_acc_from_machine_process content)
        {
            RES_unload_acc_from_machine_process OutData = new RES_unload_acc_from_machine_process();
            StringBuilder sbSQL = new StringBuilder();
            DataTable dtEmsaccstate = new DataTable();
            DataTable dtEmseqpstate = new DataTable();
            DataTable dtEmsaccstateCombineACCNo = new DataTable();
            bool is_auto_inout = get_auto_inout_val();
            // 先檢核所有的資料
            // 設備編號 設備類別 模具編號 模具類別 都不允許為空
            foreach (CDO_RequestContent_detail _CDO_RequestContent_detail in content.pTable)
            {
                //if (string.IsNullOrEmpty(_CDO_RequestContent_detail.ACCESSORYNO))
                //    throw new ServiceException(ServiceCode.ParamsValidateException, package_err("ACCESSORYNO") + package_err("IS EMPTY"));

                if (string.IsNullOrEmpty(_CDO_RequestContent_detail.ACCESSORYTYPE))
                    throw new ServiceException(ServiceCode.ParamsValidateException, package_err("ACCESSORYTYPE") + package_err("IS EMPTY"));

                if (string.IsNullOrEmpty(_CDO_RequestContent_detail.EQUIPMENTNO))
                    throw new ServiceException(ServiceCode.ParamsValidateException, package_err("EQUIPMENTNO") + package_err("IS EMPTY"));

                if (string.IsNullOrEmpty(_CDO_RequestContent_detail.EQUIPMENTTYPE))
                    throw new ServiceException(ServiceCode.ParamsValidateException, package_err("EQUIPMENTTYPE") + package_err("IS EMPTY"));

                // 20210426 Modify by Zeke for 85272: 模具上模報錯：TBLEMSEQPACCSTATE重覆索引 [金名山]
                if (IsAccSerialStatusChange(_CDO_RequestContent_detail.ACCESSORYNO.Trim(), _CDO_RequestContent_detail.ACCESSORYVERSION.Trim(), _CDO_RequestContent_detail.ACCSERIALNO.Trim()))
                {
                    //當發現ACCSerialNo不一樣時，系統顯示需按下確認的錯誤訊息
                    //『模治具編號：XXXXXXX，模治具類別：XXXXX，[右無傳入]模治具分類：XXXXXX，[左無傳入]
                    //此模治具已經做過其他功能的狀態異動，系統將自動重新讀取畫面模治具資訊!!』，
                    //按下[確認]後，系統重新讀取可上下模的所有模治具資訊，原畫面勾選結果全數清空。
                    //return new RES_unload_acc_from_machine_process() { exec_result = 2, message = package_err("ACCESSORYNO") + "：" + _CDO_RequestContent_detail.ACCESSORYNO + "，" + package_err("ACCESSORYTYPE") + "：" + _CDO_RequestContent_detail.ACCESSORYTYPE + "，" + package_err("ACC_ALREADY_STATUS_CHANGE") + "，" + package_err("SYSTEM_WILL_RELOAD_PAGE") + "！" };
                    throw new ServiceException(ServiceCode.ParamsValidateException, package_err("ACCESSORYNO") + "：" + _CDO_RequestContent_detail.ACCESSORYNO + "，" + package_err("ACCESSORYTYPE") + "：" + _CDO_RequestContent_detail.ACCESSORYTYPE + "，" + package_err("ACC_ALREADY_STATUS_CHANGE") + "，" + package_err("SYSTEM_WILL_RELOAD_PAGE") + "！");
                }
            }

            Dictionary<string, object> _parameters = new Dictionary<string, object>();
            foreach (CDO_RequestContent_detail _CDO_RequestContent_detail in content.pTable)
            {
                DataTable dtLotNo = new DataTable();
                bool GoPass = false;
                // enforce = 0 第一次執行,需要檢查設備上是否有生產批(Running)
                if (content.enforce.Equals(0))
                {
                    sbSQL.Clear();
                    sbSQL.Append(" SELECT LOTNO");
                    sbSQL.Append(" FROM TBLWIPTEMP_EQUIPMENT");
                    sbSQL.Append(" WHERE EQUIPMENTNO = '" + _CDO_RequestContent_detail.EQUIPMENTNO + "'");
                    dtLotNo = CurrentService.GetDao().Query(sbSQL.ToString());

                    if (dtLotNo == null || dtLotNo.Rows.Count == 0) GoPass = true;
                }
                else GoPass = true;

                if (GoPass)
                {
                    //'取出tblEMSEQPACCState,tblEMSAccessoryState資料
                    sbSQL.Clear();
                    sbSQL.Append(" Select A.* , B.ACCSERIALNO ,B.Locatorno ");
                    sbSQL.Append(" From TBLEMSEQPACCSTATE A Left Join TBLEMSACCESSORYSTATE  B ");
                    sbSQL.Append(" On A.ACCESSORYNO = B.ACCESSORYNO Where  1=1");
                    sbSQL.Append(" And A.EquipmentNo = '" + _CDO_RequestContent_detail.EQUIPMENTNO + "'");

                    if (!string.IsNullOrEmpty(_CDO_RequestContent_detail.ACCESSORYNO))
                        sbSQL.Append("And  A.AccessoryNo = '" + _CDO_RequestContent_detail.ACCESSORYNO + "'");

                    dtEmseqpstate = CurrentService.GetDao().Query(sbSQL.ToString());

                    //20220309 add by 翰鴻 for M#102577
                    //当手动输入时间 >= 模具下模标准时间时，系统跳出弹窗，提示“即将达到预设下模时间，请加快下模速度或修改预设数据”
                    #region
                    sbSQL.Clear();
                    sbSQL.Append("select COALESCE(F.UnloadTime, 0) UnloadTime                                                                           ");
                    sbSQL.Append("FROM TBLEMSEQPACCSTATE A                                                                                  ");
                    sbSQL.Append("LEFT JOIN tblACCChangeTime F ON A.AccessoryNo = F.AccessoryNo AND A.AccessoryVersion = F.AccessoryVersion ");
                    sbSQL.Append("where A.EquipmentNo = ? and A.AccessoryNo = ? and A.AccessoryVersion = ?");

                    _parameters.Clear();
                    _parameters.Add("EquipmentNo", _CDO_RequestContent_detail.EQUIPMENTNO);
                    _parameters.Add("AccessoryNo", _CDO_RequestContent_detail.ACCESSORYNO);
                    _parameters.Add("AccessoryVersion", _CDO_RequestContent_detail.ACCESSORYVERSION);

                    int UnloadTime = 0;
                    //if( CurrentService.GetDao().Query1RecordInt(Convert.ToString(sbSQL), _parameters).HasValue)
                    //{
                    //    UnloadTime = CurrentService.GetDao().Query1RecordInt(Convert.ToString(sbSQL), _parameters).Value;
                    //}
                    DataTable dt_time = CurrentService.GetDao().Query(Convert.ToString(sbSQL), _parameters);
                    if (dt_time.Rows.Count > 0)
                    {
                        UnloadTime = Convert.ToInt32(dt_time.Rows[0]["UnloadTime"]);
                    }
                    if (content.ACUnLoadTime >= UnloadTime)
                    {
                        //模治具編號 {0} 即將達到預設下模時間，請加快下模速度或修改預設數據
                        OutData.message += this.CurrentService.GetResource("[%Module_ACC.SPEED_UNLOAD_ACC_LOAD%]", _CDO_RequestContent_detail.ACCESSORYNO);
                        OutData.message += "<br>";
                    }
                    #endregion

                    //  '更改 ChangeACCState
                    //  '刪除tblEMSEQPACCState
                    //  '更新組合模治具下所有模治具
                    string now_time = DateTime.Now.ToString("yyyy/MM/dd H:mm:ss");
                    foreach (DataRow _DataRow in dtEmseqpstate.Rows)
                    {
                        //  '更改 ChangeACCState
                        if(is_auto_inout)
                            change_ACC_state_auto_InOut(_DataRow, now_time, content.ACUnLoadTime);
                        else
                            change_ACC_state(_DataRow, now_time, content.ACUnLoadTime);

                        // 20220207 add by 翰鴻 for#102969：
                        update_ACC_LastLocator(_DataRow, _CDO_RequestContent_detail.TempLocatorNo);

                        //'刪除tblEMSEQPACCState
                        sbSQL.Clear();
                        sbSQL.Append(" Delete From tblEMSEQPACCState Where AccessoryNo = '" + _DataRow["AccessoryNo"] + "' And EquipmentNo = '" + _CDO_RequestContent_detail.EQUIPMENTNO + "'");
                        CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());

                        // 修改模治具下模数量
                        sbSQL.Clear();
                        sbSQL.Append($@" select count(1) from TBLCUSMoldMoves where AccessoryNo = '{_DataRow["AccessoryNo"]}' and AccessoryVersion = '{_DataRow["ACCESSORYVERSION"]}' ");
                        if (CurrentService.GetDao().Query1RecordInt(sbSQL.ToString()) > 0)
                        {
                            sbSQL.Clear();
                            sbSQL.Append($@" UPDATE TBLCUSMoldMoves
                                     SET HistoricalMoldDowns = HistoricalMoldDowns + 1,CurrentMoldDowns = CurrentMoldDowns + 1
                                     WHERE AccessoryNo = '{_DataRow["AccessoryNo"]}' and AccessoryVersion = '{_DataRow["ACCESSORYVERSION"]}' ");
                            CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
                        }
                        else
                        {
                            sbSQL.Clear();
                            sbSQL.Append($@" INSERT TBLCUSMoldMoves ( Creator,CreateDate,AccessoryNo,AccessoryVersion,
                                                              HistoryCount,CurrentMoldUps,HistoricalMoldDowns,CurrentMoldDowns,
                                                              repairStatus,FutureUse,modifiedDate) 
                                     VALUES ('{CurrentService.GetServiceInfo().account}',Getdate(),'{_DataRow["AccessoryNo"]}','{_DataRow["ACCESSORYVERSION"]}',
                                             0,0,1,1,
                                             null,0,null) ");
                            CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
                        }

                        //'更新組合模治具下所有模治具
                        sbSQL.Clear();
                        sbSQL.Append(" Select A.CombineACCNo , B.ACCSerialNo , B.AccessoryNo ");
                        sbSQL.Append(" From tblEMSCombineACCState A Left Join  tblEMSAccessoryState  B  ");
                        sbSQL.Append(" On A.AccessoryNo = B.AccessoryNo ");
                        sbSQL.Append(" Where 1=1 And CombineACCNo = '" + _DataRow["AccessoryNo"] + "'");

                        dtEmsaccstateCombineACCNo = CurrentService.GetDao().Query(sbSQL.ToString());

                        foreach (DataRow _Datarow2 in dtEmsaccstateCombineACCNo.Rows)
                        {
                            if (is_auto_inout)
                                change_ACC_state_auto_InOut(_Datarow2, now_time, content.ACUnLoadTime);
                            else
                                change_ACC_state(_Datarow2, now_time, content.ACUnLoadTime);
                        }
                    }

                    // 更新 TBLWIPCONT_ACCESSORY 
                    updContAccessory(_CDO_RequestContent_detail);
                    OutData.exec_result = 1;
                    

                }
                else
                {
                    OutData.exec_result = 0;
                    string strLotNo = "";
                    foreach(DataRow drLotNo in dtLotNo.Rows )
                    {
                        if (strLotNo == "") strLotNo = Convert.ToString(drLotNo[0]);
                        else strLotNo = strLotNo + "、<br>" + Convert.ToString(drLotNo[0]);
                    }
                    OutData.message = package_err("EQUIPMENTNO") + ":" + _CDO_RequestContent_detail.EQUIPMENTNO + " " + 
                        package_err("THERERUNING") + "(" + strLotNo +  ")," + package_err("SUGGEST1_1") + "," + 
                        package_err("SUGGEST1_2") + "," + package_err("continue ask") + "?" ;
                    /* 設備編號：xxxx 仍有生產中的生產批(XXX、xxx)，建議先執行出站報工後再做模治具更換作業，否則生產批出站的模治具壽命耗用將會被算在新的模治具上，是否繼續? */
                    return OutData;
                }
            }            
            return OutData;
        }
        /// <summary>
        /// 改變模具狀態
        /// </summary>
        /// <param name="default_time"></param>           預設時間         M#65970 add by a991241234
        private void change_ACC_state(DataRow _DataRow,string default_time, int ACUnLoadTime)
        {
            StringBuilder sbSQL = new StringBuilder();
            DataTable dtEmsaccstate = new DataTable();
            DataTable dtEmseqpstate = new DataTable();

            sbSQL.Clear();
            sbSQL.Append(" Select a.ACCSerialNo,a.AccessoryState,a.StartTime,a.UserNo,a.Description,a.AccessoryNo,a.AccessoryVersion,a.APPLYTIME,a.ACCUMULATEQTY,a.ACLoadTime,b.* ");
            sbSQL.Append(" From tblEMSAccessoryState a, TBLEQPACCESSORYBASIS b ");
            sbSQL.Append(" Where a.AccessoryNo = '" + _DataRow["AccessoryNo"] + "' And a.AccessoryVersion = (select AccessoryVersion from TBLEQPACCESSORYBASIS where AccessoryNo = '" + _DataRow["AccessoryNo"] + "' and CURVERSION = 1) ");
            sbSQL.Append(" and a.AccessoryNo = b.AccessoryNo and a.AccessoryVersion = b.AccessoryVersion");

            //檢查是否有資料
            dtEmsaccstate = CurrentService.GetDao().Query(sbSQL.ToString());

            if (dtEmsaccstate.Rows.Count.Equals(0))
                throw new ServiceException(ServiceCode.ParamsValidateException, package_err("tblEMSAccessoryState") + package_err("DATA NOT FOUND"));

            sbSQL.Clear();
            // 20230424 modify by xingl for M#0133684: CHANGETTOOLTIME改为CHANGETOOLTIME
            sbSQL.Append("Insert into tblEMSAccessoryStateLog(ACCSerialNo,AccessoryNo,AccessoryVersion,AccessoryState,AccessoryType,UserNo,StartTime,EndTime,Description,APPLYTIME,ACCUMULATEQTY, CHANGETOOLTIME) ");
            sbSQL.Append("Values('" + dtEmsaccstate.Rows[0]["ACCSerialNo"] + "','" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "','" + dtEmsaccstate.Rows[0]["AccessoryVersion"] + "','" + dtEmsaccstate.Rows[0]["AccessoryState"] + "','" + dtEmsaccstate.Rows[0]["AccessoryType"] + "','" + dtEmsaccstate.Rows[0]["UserNo"] + "', To_Date('" + ((DateTime)(dtEmsaccstate.Rows[0]["StartTime"])).ToString("yyyy/MM/dd H:mm:ss") + "', 'YYYY/MM/DD HH24:MI:SS'),'"+default_time+ "','" + dtEmsaccstate.Rows[0]["Description"] + "', ");

            if (!dtEmsaccstate.Rows[0]["APPLYTIME"].Equals(DBNull.Value))
                sbSQL.Append("To_Date('" + ((DateTime)(dtEmsaccstate.Rows[0]["APPLYTIME"])).ToString("yyyy/MM/dd H:mm:ss") + "', 'YYYY/MM/DD HH24:MI:SS'),");
            else
                sbSQL.Append("'"+default_time+ "',");
            //20220309 modify by 翰鴻 for M#102577
            if (!dtEmsaccstate.Rows[0]["ACCUMULATEQTY"].Equals(DBNull.Value))
                sbSQL.Append(dtEmsaccstate.Rows[0]["ACCUMULATEQTY"]);
            else
                sbSQL.Append("0");
            sbSQL.Append(",");
            if (!dtEmsaccstate.Rows[0]["ACLoadTime"].Equals(DBNull.Value))
                sbSQL.Append(dtEmsaccstate.Rows[0]["ACLoadTime"]);
            else
                sbSQL.Append("0");
            sbSQL.Append(")");
            CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());

            // '變更State,現況變更要重新產生一新的SerialNo
            sbSQL.Clear();
            sbSQL.Append(" Update tblEMSAccessoryState Set AccessoryState = 1,");
            sbSQL.Append(" StartTime = '"+default_time+ "',");
            sbSQL.Append(" ACCSerialNo ='" + InitACCSerialNo() + "',");
            sbSQL.Append(" UserNo = '" + BaseService._ServiceInfo.account + "',");
            //20220309 modify by 翰鴻 for M#102577
            sbSQL.Append(" Description = '',");
            sbSQL.Append(" ACUnLoadTime = '" + ACUnLoadTime + "'");
            sbSQL.Append(" Where AccessoryNo = '" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "' And AccessoryVersion = '" + dtEmsaccstate.Rows[0]["AccessoryVersion"] + "'");

            CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
            //write log tblEMSEQPACCState to tblEMSEQPACCStateLog for M#65970 add by a991241234
            sbSQL.Clear();
            sbSQL.Append("SELECT * FROM tblEMSEQPACCState WHERE AccessoryNo = '" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "'");
            DataTable tblEMSEQPACCState_data = this.CurrentService.GetDao().Query(sbSQL.ToString());
            if (tblEMSEQPACCState_data.Rows.Count > 0)
            {
                sbSQL.Clear();
                string tblEMSEQPACCState_cloum_sql = "'" + dtEmsaccstate.Rows[0]["ACCSerialNo"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["EQUIPMENTTYPE"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["EQUIPMENTNO"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["ACCESSORYTYPE"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["ACCESSORYNO"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["ACCESSORYVERSION"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["USERNO"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + Convert.ToDateTime(tblEMSEQPACCState_data.Rows[0]["STARTTIME"]).ToString("yyyy/MM/dd H:mm:ss") + "',";
                tblEMSEQPACCState_cloum_sql += "'"+default_time+ "'";
                sbSQL.Append("INSERT INTO TBLEMSEQPACCSTATELOG");
                sbSQL.Append("   (ACCSERIALNO,EQUIPMENTTYPE,EQUIPMENTNO,ACCESSORYTYPE,ACCESSORYNO,ACCESSORYVERSION,USERNO,STARTTIME,ENDTIME)    ");
                sbSQL.Append("VALUES");
                sbSQL.Append("  (" + tblEMSEQPACCState_cloum_sql + ")  ");
                CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
            }
        }
        /// <summary>
        /// 改變模具狀態 自動入庫
        /// </summary>
        private void change_ACC_state_auto_InOut(DataRow _DataRow, string default_time, int ACUnLoadTime)
        {
            StringBuilder sbSQL = new StringBuilder();
            DataTable dtEmsaccstate = new DataTable();
            sbSQL.Clear();
            //20220309 modify by 翰鴻 for M#102577
            sbSQL.Append(" Select a.ACCSerialNo,a.AccessoryState,a.StartTime,a.UserNo,a.Description,a.AccessoryNo,a.AccessoryVersion,a.APPLYTIME,a.ACCUMULATEQTY,a.ACLoadTime,b.* ");
            sbSQL.Append(" From tblEMSAccessoryState a, TBLEQPACCESSORYBASIS b ");
            sbSQL.Append(" Where a.AccessoryNo = '" + _DataRow["AccessoryNo"] + "' And a.AccessoryVersion = (select AccessoryVersion from TBLEQPACCESSORYBASIS where AccessoryNo = '" + _DataRow["AccessoryNo"] + "' and CURVERSION = 1) ");
            sbSQL.Append(" and a.AccessoryNo = b.AccessoryNo and a.AccessoryVersion = b.AccessoryVersion");
            //檢查是否有資料
            dtEmsaccstate = CurrentService.GetDao().Query(sbSQL.ToString());
            if (dtEmsaccstate.Rows.Count.Equals(0))
                throw new ServiceException(ServiceCode.ParamsValidateException, package_err("tblEMSAccessoryState") + package_err("DATA NOT FOUND"));
            string apply_time = default_time;
            string acc_umulate_qty = "0";
            if (dtEmsaccstate.Rows[0]["APPLYTIME"].Equals(DBNull.Value) == false)
                apply_time = Convert.ToDateTime(dtEmsaccstate.Rows[0]["APPLYTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
            if (!dtEmsaccstate.Rows[0]["ACCUMULATEQTY"].Equals(DBNull.Value))
                acc_umulate_qty = dtEmsaccstate.Rows[0]["ACCUMULATEQTY"].ToString();
            //20220309 modify by 翰鴻 for M#102577
            string ACLoadTime = "0";
            if (!dtEmsaccstate.Rows[0]["ACLoadTime"].Equals(DBNull.Value))
                ACLoadTime = Convert.ToString(dtEmsaccstate.Rows[0]["ACLoadTime"]);
            sbSQL.Clear();
            sbSQL.Append("Insert into tblEMSAccessoryStateLog(ACCSerialNo,AccessoryNo,AccessoryVersion,AccessoryState,AccessoryType,UserNo,StartTime,EndTime,Description,APPLYTIME,ACCUMULATEQTY,CHANGETOOLTIME) ");
            sbSQL.Append("Values('" + dtEmsaccstate.Rows[0]["ACCSerialNo"] + "','" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "','" + dtEmsaccstate.Rows[0]["AccessoryVersion"] + "','" + dtEmsaccstate.Rows[0]["AccessoryState"] + "','" + dtEmsaccstate.Rows[0]["AccessoryType"] + "','" + dtEmsaccstate.Rows[0]["UserNo"] + "', To_Date('" + ((DateTime)(dtEmsaccstate.Rows[0]["StartTime"])).ToString("yyyy/MM/dd H:mm:ss") + "', 'YYYY/MM/DD HH24:MI:SS'),'" + default_time + "','" + dtEmsaccstate.Rows[0]["Description"] + "', '" + apply_time + "'," + acc_umulate_qty + "," + ACLoadTime + ")");
            this.CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
            List<string> state_list = new List<string>() { "1", "0" };
            string last_acc_ser_no = "";
            for(int i = 0; i < state_list.Count;i++)
            {
                string state = state_list[i];
                string acc_ser_no = InitACCSerialNo();
                sbSQL.Clear();
                sbSQL.Append(" Update tblEMSAccessoryState Set AccessoryState = " + state + ",");
                sbSQL.Append(" StartTime = '" + default_time + "' ,");
                sbSQL.Append(" ACCSerialNo ='" + acc_ser_no + "',");
                sbSQL.Append(" UserNo = '" + BaseService._ServiceInfo.account + "',");
                //20220309 modify by 翰鴻 for M#102577
                sbSQL.Append(" Description = '',");
                sbSQL.Append(" ACUnLoadTime = '" + ACUnLoadTime + "'");
                sbSQL.Append(" Where AccessoryNo = '" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "' And AccessoryVersion = '" + dtEmsaccstate.Rows[0]["AccessoryVersion"] + "'");
                CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
                if (i < (state_list.Count - 1))
                {
                    sbSQL.Clear();
                    sbSQL.Append("Insert into tblEMSAccessoryStateLog(ACCSerialNo,AccessoryNo,AccessoryVersion,AccessoryState,AccessoryType,UserNo,StartTime,EndTime,Description,APPLYTIME,ACCUMULATEQTY,CHANGETOOLTIME) ");
                    sbSQL.Append("Values('" + acc_ser_no + "','" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "','" + dtEmsaccstate.Rows[0]["AccessoryVersion"] + "','" + state + "','" + dtEmsaccstate.Rows[0]["AccessoryType"] + "','" + dtEmsaccstate.Rows[0]["UserNo"] + "', To_Date('" + ((DateTime)(dtEmsaccstate.Rows[0]["StartTime"])).ToString("yyyy/MM/dd H:mm:ss") + "', 'YYYY/MM/DD HH24:MI:SS'),'" + default_time + "','" + dtEmsaccstate.Rows[0]["Description"] + "', '" + apply_time + "'," + acc_umulate_qty + "," + ACLoadTime + ")");
                    this.CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
                }
                else
                {
                    last_acc_ser_no = acc_ser_no;
                }
            }
            //write log tblEMSEQPACCState to tblEMSEQPACCStateLog for M#65970 add by a991241234
            sbSQL.Clear();
            sbSQL.Append("SELECT * FROM tblEMSEQPACCState WHERE AccessoryNo = '" + dtEmsaccstate.Rows[0]["AccessoryNo"] + "'");
            DataTable tblEMSEQPACCState_data = this.CurrentService.GetDao().Query(sbSQL.ToString());
            if (tblEMSEQPACCState_data.Rows.Count > 0)
            {
                sbSQL.Clear();
                string tblEMSEQPACCState_cloum_sql = "'" + last_acc_ser_no + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["EQUIPMENTTYPE"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["EQUIPMENTNO"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["ACCESSORYTYPE"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["ACCESSORYNO"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["ACCESSORYVERSION"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + tblEMSEQPACCState_data.Rows[0]["USERNO"] + "',";
                tblEMSEQPACCState_cloum_sql += "'" + Convert.ToDateTime(tblEMSEQPACCState_data.Rows[0]["STARTTIME"]).ToString("yyyy/MM/dd H:mm:ss") + "',";
                tblEMSEQPACCState_cloum_sql += "'" + default_time + "'";
                sbSQL.Append("INSERT INTO TBLEMSEQPACCSTATELOG");
                sbSQL.Append("   (ACCSERIALNO,EQUIPMENTTYPE,EQUIPMENTNO,ACCESSORYTYPE,ACCESSORYNO,ACCESSORYVERSION,USERNO,STARTTIME,ENDTIME)    ");
                sbSQL.Append("VALUES");
                sbSQL.Append("  (" + tblEMSEQPACCState_cloum_sql + ")  ");
                CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
            }
        }
        /// <summary>
        /// 判斷是否自動出入庫
        /// </summary>
        /// <returns></returns>
        private bool get_auto_inout_val()
        {
            bool is_auto = false;
            string auto_val = CommonUtilities.GetParameterValue("EQPACCINVAutoInOut");
            if (string.IsNullOrEmpty(auto_val) == false)
            {
                if (auto_val.Equals("Y") || auto_val.Equals("1"))
                    is_auto = true;
            }
            return is_auto;
        }
        /// <summary>
        /// 更新 TBLWIPCONT_ACCESSORY 
        /// </summary>
        /// <param name="_CDO_RequestContent_detail"></param>
        private void updContAccessory(CDO_RequestContent_detail _CDO_RequestContent_detail)
        {
            StringBuilder sbSQL = new StringBuilder();

            // 取得設備上的生產批 LOGGROUPSERIAL
            sbSQL.Append("SELECT LOGGROUPSERIAL, EQUIPMENTNO ");
            sbSQL.Append("FROM TBLWIPTEMP_EQUIPMENT ");
            sbSQL.Append("WHERE EQUIPMENTNO ='" + _CDO_RequestContent_detail.EQUIPMENTNO + "' ");

            DataTable onEqPLot = CurrentService.GetDao().Query(sbSQL.ToString());
            if (onEqPLot.Rows.Count > 0)
            {
                foreach (DataRow row in onEqPLot.Rows)
                {
                    sbSQL.Clear();
                    sbSQL.Append(" UPDATE TBLWIPCONT_ACCESSORY");
                    sbSQL.Append(" SET ENDTIME = SYSDATE");
                    sbSQL.Append(" WHERE EQUIPMENTNO = '" + _CDO_RequestContent_detail.EQUIPMENTNO + "'");
                    sbSQL.Append(" AND LOGGROUPSERIAL = '" + Convert.ToString(row["LOGGROUPSERIAL"]) + "'");
                    sbSQL.Append(" AND ACCESSORYNO = '" + _CDO_RequestContent_detail.ACCESSORYNO + "' AND ENDTIME IS NULL ");

                    CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());
                }

                // 20240126 add by duzm M#0156362: 【好利来】正式区，模治具寿命扣除量与出站量不一致
                // 处理未下模前出站后不在今日生产任务中的记录
                sbSQL.Clear();
                sbSQL.Append("UPDATE TBLWIPCONT_ACCESSORY SET ENDTIME = SYSDATE WHERE accessoryno = '" + _CDO_RequestContent_detail.ACCESSORYNO + "' AND EQUIPMENTNO = '" + _CDO_RequestContent_detail.EQUIPMENTNO + "'  AND ENDTIME IS NULL");
                CurrentService.GetDao().ExecuteNonQuery(sbSQL.ToString());

            }
        }
        
        /// <summary>
        /// 將產生ACC Serial
        /// </summary>
        /// <returns></returns>
        private string InitACCSerialNo()
        {
            string ACCSerialNo = "",strYear, strMonth ;
            var strTempDate = new object();
            var SerialNo_ACCSTATELOG = new object();
            var SerialNo_ACCSTATEDATE = new object(); 
            StringBuilder sbSQL = new StringBuilder();
            int intSerial = 1;
            strYear = DateTime.Now.Year.ToString().Substring(2,2); //2018 取18
            strMonth = DateTime.Now.Month.ToString("d2");

            if ("oracle".Equals(CurrentService.GetDao().DBType.ToLower()))
            {
                
                //'1.檢查Sequenct物件是否存在
                string sequenceName = "ACCSTATELOG" + strYear + strMonth;
                sbSQL.Clear();
                sbSQL.Append("Select Object_Name from User_Objects Where Object_Type = 'SEQUENCE' And Object_Name = '" + sequenceName + "'");
                DataTable dtTmp = CurrentService.GetDao().Query(sbSQL.ToString());

                if (dtTmp == null || dtTmp.Rows.Count == 0)
                {
                    //0071379: 模治具上下模出现问题
                    sbSQL.Clear();
                    sbSQL.Append("Create Sequence " + sequenceName + " Increment by 1 " + " Start with 1 " + " MaxValue 99999999 MinValue 0 Cycle Cache 20");

                    //'呼叫執行SQL指令
                    CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());
                }
                dtTmp.Clear();

                //'2.取得Sequence的流水號
                sbSQL.Clear();
                sbSQL.Append("Select " + sequenceName + ".NextVal as NextValue From Dual");
                dtTmp = CurrentService.GetDao().Query(sbSQL.ToString());

                if (dtTmp!= null && dtTmp.Rows.Count > 0 ) intSerial = Convert.ToInt32(dtTmp.Rows[0]["NextValue"]);
                else throw new ServiceException( ServiceCode.DataAbnormalException, package_err("Get NextVal of sequence object:") + sequenceName + package_err("fail") + " !");
                ;
            }
            else
            {
                sbSQL.Clear();
                sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATEDATE'");
                SerialNo_ACCSTATEDATE = CurrentService.GetDao().Query1RecordColumn(sbSQL.ToString());

                //防呆
                if (SerialNo_ACCSTATEDATE == null)
                {
                    //'填入新的日期,格式化存入(ex:0412,因Serial為數值故無法存入0412,故一律前面加上1)
                    sbSQL.Clear();
                    sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATEDATE',1" + strYear + strMonth + ")");
                    //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                    CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());

                    // '月的序號
                    sbSQL.Clear();
                    sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATELOG'");
                    SerialNo_ACCSTATELOG = CurrentService.GetDao().Query1RecordColumn(sbSQL.ToString());

                    if (SerialNo_ACCSTATELOG == null)
                    {
                        sbSQL.Clear();
                        sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATELOG', 1)");
                        //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                        CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());
                    }
                    else
                    {
                        sbSQL.Clear();
                        sbSQL.Append("Update tblSysSerialNo Set SerialNo = 1 Where FunctionName = 'ACCSTATELOG'");
                        //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                        CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());
                    }

                    intSerial = 1;
                }
                else
                {
                    strTempDate = SerialNo_ACCSTATEDATE;

                    //'更新日期,格式化存入(ex:041208,因Serial為數值故無法存入041208,故一律前面加上1)
                    sbSQL.Clear();
                    sbSQL.Append("Update tblSysSerialNo Set SerialNo = 1" + strYear + strMonth + " Where FunctionName = 'ACCSTATEDATE'");
                    //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                    CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());

                    // '比較日期

                    if ((strTempDate.ToString()).Equals("1" + strYear + strMonth)) //'同月
                    {
                        // '取出月的序號
                        sbSQL.Clear();
                        sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATELOG'");
                        SerialNo_ACCSTATELOG = CurrentService.GetDao().Query1RecordColumn(sbSQL.ToString());
                        if (SerialNo_ACCSTATELOG == null)
                        {
                            sbSQL.Clear();
                            sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATELOG', 1)");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());

                            intSerial = 1;
                        }
                        else
                        {
                            // '填入新SerialNo
                            sbSQL.Clear();
                            sbSQL.Append("Update tblSysSerialNo Set SerialNo = " + (Convert.ToInt32(SerialNo_ACCSTATELOG) + 1) + " Where FunctionName = 'ACCSTATELOG'");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());

                            intSerial = (Convert.ToInt32(SerialNo_ACCSTATELOG) + 1);
                        }
                    }
                    else
                    {
                        //'月的序號
                        sbSQL.Clear();
                        sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATELOG'");
                        SerialNo_ACCSTATELOG = CurrentService.GetDao().Query1RecordColumn(sbSQL.ToString());

                        if (SerialNo_ACCSTATELOG == null)
                        {
                            sbSQL.Clear();
                            sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATELOG', 1)");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());
                        }
                        else
                        {
                            sbSQL.Clear();
                            sbSQL.Append("Update tblSysSerialNo Set SerialNo = 1 Where FunctionName = 'ACCSTATELOG'");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            CurrentService.GetDao().ExecuteImmediately(sbSQL.ToString());
                        }
                        intSerial = 1;
                    }
                }
            }

            // 'ACC Serial No編碼規則
            // '一碼前置字元 +　西元年後兩碼　＋ 兩碼月份 + 七碼流水號
            // '每月流水號歸零，例如S06010000001

            ACCSerialNo = "A" + strYear + strMonth + intSerial.ToString("0000000");
            return ACCSerialNo;
        }

        /// <summary>
        /// 封裝語系
        /// 模組名稱 + errvalue
        /// </summary>
        /// <param name="errvalue"></param>
        /// <returns></returns>
        private string package_err(string errvalue)
        {
            //return CurrentService.GetResource("[%" + CurrentService.GetServiceInfo().service_module + "." + errvalue + "%]");
            return CurrentService.GetResource("[%Module_ACC." + errvalue + "%]");
        }

        /// <summary>
        /// 判斷模治具是否已經做過其他功能的狀態異動
        /// </summary>
        /// <param name="accessoryNo">模具編號</param>
        /// <param name="accessoryVersion">模具版本</param>
        /// <param name="accserialNo">模具序號</param>
        /// <returns></returns>
        /// <remarks>
        /// 20210426 Modify by Zeke for 85272: 模具上模報錯：TBLEMSEQPACCSTATE重覆索引 [金名山]
        /// </remarks>
        private bool IsAccSerialStatusChange(string accessoryNo, string accessoryVersion, string accserialNo)
        {
            // 避免前端未準備accserialNo傳入
            if (String.IsNullOrWhiteSpace(accserialNo))
                return false;

            string strAccserialNo = string.Empty;
            StringBuilder sbSQL = new StringBuilder();

            sbSQL.Append(" SELECT ACCSERIALNO FROM TBLEMSACCESSORYSTATE WHERE ACCESSORYNO = '" + accessoryNo + "' AND ACCESSORYVERSION = '" + accessoryVersion + "' ");
            var getAccserialNo = CurrentService.GetDao().Query1RecordColumn(Convert.ToString(sbSQL));

            if (getAccserialNo != null && getAccserialNo != DBNull.Value)
            {
                strAccserialNo = Convert.ToString(getAccserialNo);
            }
            
            return !accserialNo.ToUpper().Trim().Equals(strAccserialNo.ToUpper().Trim());
        }

        private void update_ACC_LastLocator(DataRow _DataRow, string TempLocatorNo)
        {
            StringBuilder _SQL = new StringBuilder();
            DataTable dtEmsaccstate = new DataTable();
            Dictionary<string, object> _parameters = new Dictionary<string, object>();
            _SQL.Clear();
            _SQL.Append(" Select a.ACCSerialNo,a.AccessoryState,a.StartTime,a.UserNo,a.Description,a.AccessoryNo,a.AccessoryVersion,a.APPLYTIME,a.ACCUMULATEQTY,b.* ");
            _SQL.Append(" From tblEMSAccessoryState a, TBLEQPACCESSORYBASIS b ");
            _SQL.Append(" Where a.AccessoryNo = ? And a.AccessoryVersion = (select AccessoryVersion from TBLEQPACCESSORYBASIS where AccessoryNo = ? and CURVERSION = 1) ");
            _SQL.Append(" and a.AccessoryNo = b.AccessoryNo and a.AccessoryVersion = b.AccessoryVersion");

            _parameters.Add("AccessoryNo", _DataRow["AccessoryNo"]);
            _parameters.Add("AccessoryNo2", _DataRow["AccessoryNo"]);

            //檢查是否有資料
            dtEmsaccstate = CurrentService.GetDao().Query(Convert.ToString(_SQL), _parameters);
            if (dtEmsaccstate.Rows.Count.Equals(0))
                throw new ServiceException(ServiceCode.ParamsValidateException, package_err("tblEMSAccessoryState") + package_err("DATA NOT FOUND"));

            _SQL.Clear();
            _SQL.Append("UPDATE TBLEMSACCESSORYSTATE ");
            _SQL.Append("SET TempLocatorNo = ? ");
            _SQL.Append("WHERE ACCESSORYNO = ? AND ACCESSORYVERSION = ? ");

            _parameters.Clear();
            _parameters.Add("TempLocatorNo", TempLocatorNo);
            _parameters.Add("ACCESSORYNO", _DataRow["AccessoryNo"]);
            _parameters.Add("ACCESSORYVERSION", dtEmsaccstate.Rows[0]["AccessoryVersion"]);

            this.CurrentService.GetDao().ExecuteNonQuery(Convert.ToString( _SQL), _parameters);
        }
    }
}
